package cn.edu.seu.cose.qol.model.dao.database.question;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.LinkedList;

import cn.edu.seu.cose.qol.model.dao.database.QolSchemaConnectionFactory;
import cn.edu.seu.cose.qol.model.po.question.Questionnaire;
import cn.edu.seu.cose.qol.model.po.question.TrueOrFalse;


public class TrueOrFalseDataAccess {
	
	private static final String COLUMN_TORF_ID = "question_torf_id";
	
	private static final String COLUMN_QUESTIONNAIRE_ID =
			"fk_questionnaire_id";
	
	private static final String COLUMN_QUESTION_TITLE = "question_title";
	
	private static final String STATEMENT_RETRIEVE_TORF_BY_ID =
			"SELECT * FROM qol_question_torf WHERE question_torf_id=?;";
	
	private static final String STATEMENT_RETRIEVE_TORFS =
			"SELECT * FROM qol_question_torf;";
	
	private static final String STATEMENT_RETRIEVE_TORFS_BY_LIMIT =
			"SELECT * FROM qol_question_torf LIMIT ?,?;";
	
	private static final String STATEMENT_RETRIEVE_TORFS_RANDOMLY =
			"SELECT * FROM qol_question_torf ORDER BY RAND(NOW()) LIMIT ?;";
	
	private static final String STATEMENT_CREATE_TORF =
			"INSERT INTO qol_question_torf (question_torf_id, " +
			"fk_questionnaire_id, question_title) " +
			"VALUES (?, ?);";
	
	private static final String STATEMENT_DELETE_TORF_BY_ID =
			"DELETE FROM qol_question_torf WHERE question_torf_id=?;";
	
	private static final String STATEMENT_UPDATE_TORF =
			"UPDATE qol_question_torf " +
			"SET question_title=? WHERE question_torf_id=?;";
	
	private static final String
			STATEMENT_DELETE_TORFS_BY_QUESTIONNAIRE_ID =
				"DELETE FROM qol_question_torf WHERE fk_questionnaire_id=?;";
	
	private static TrueOrFalseDataAccess _instance;
	
	private QolSchemaConnectionFactory _factory;
	
	private QuestionnaireDataAccess _questionnaireDataAccess;
	
	public static TrueOrFalseDataAccess getInstance() {
		if (_instance == null) {
			_instance = new TrueOrFalseDataAccess();
		}
		
		return _instance;
	}
	
	protected TrueOrFalseDataAccess() {
		_factory = QolSchemaConnectionFactory.getInstance();
		_questionnaireDataAccess = QuestionnaireDataAccess.getInstance();
	}
	
	public TrueOrFalse getTrueOrFalseById(int id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_RETRIEVE_TORF_BY_ID);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}

			TrueOrFalse torf = getTrueOrFalseByResultSet(rs);
			return torf;
		} finally {
			connection.close();
		}
	}
	
	public Collection<TrueOrFalse> getTrueOrFalses() throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_RETRIEVE_TORFS);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<TrueOrFalse> trueOrFalses = new LinkedList<TrueOrFalse>();
			while (rs.next()) {
				TrueOrFalse torf = getTrueOrFalseByResultSet(rs);
				trueOrFalses.add(torf);
			}
			return trueOrFalses;
		} finally {
			connection.close();
		}
	}
	
	public Collection<TrueOrFalse> getTrueOrFalses(long offset,
			long row) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_RETRIEVE_TORFS_BY_LIMIT);
			ps.setLong(1, offset);
			ps.setLong(2, row);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<TrueOrFalse> trueOrFalses = new LinkedList<TrueOrFalse>();
			while (rs.next()) {
				TrueOrFalse torf = getTrueOrFalseByResultSet(rs);
				trueOrFalses.add(torf);
			}
			return trueOrFalses;
		} finally {
			connection.close();
		}
	}
	
	public Collection<TrueOrFalse> getTrueOrFalses(long row, boolean random)
			throws SQLException {
		if (!random) {
			return getTrueOrFalses(0, row);
		}
		
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_TORFS_RANDOMLY);
			ps.setLong(1, row);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<TrueOrFalse> trueOrFalses = new LinkedList<TrueOrFalse>();
			while (rs.next()) {
				TrueOrFalse torf = getTrueOrFalseByResultSet(rs);
				trueOrFalses.add(torf);
			}
			return trueOrFalses;
		} finally {
			connection.close();
		}
	}
	
	public long createNewTrueOrFalse(TrueOrFalse torf) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_CREATE_TORF, Statement.RETURN_GENERATED_KEYS);
			long torfId = torf.getTrueOrFalseId();
			String questionTitle = torf.getQuestionTitle();
			ps.setLong(1, torfId);
			ps.setString(2, questionTitle);
			ps.executeUpdate();

			ResultSet rs = ps.getGeneratedKeys();
			if (!rs.next()) {
				return -1;
			}
			return rs.getLong(1);
		} finally {
			connection.close();
		}
	}
	
	public void updateTrueOrFalse(TrueOrFalse torf) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_UPDATE_TORF);
			long torfId = torf.getTrueOrFalseId();
			String questionTitle = torf.getQuestionTitle();
			ps.setString(1, questionTitle);
			ps.setLong(2, torfId);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteTrueOrFalseById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(STATEMENT_DELETE_TORF_BY_ID);
			ps.setLong(1, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteTrueOrFalseByQuestionnaireId(int questionnaireId)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_TORFS_BY_QUESTIONNAIRE_ID);
			ps.setInt(1, questionnaireId);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	private TrueOrFalse getTrueOrFalseByResultSet(ResultSet rs)
			throws SQLException {
		long torfId = rs.getLong(COLUMN_TORF_ID);
		int questionnaireId = rs.getInt(COLUMN_QUESTIONNAIRE_ID);
		String questionTitle = rs.getString(COLUMN_QUESTION_TITLE);

		Questionnaire questionnaire =
				_questionnaireDataAccess.getQuestionnaireById(questionnaireId);
		
		TrueOrFalse torf = new TrueOrFalse();
		torf.setQuestionnaire(questionnaire);
		torf.setQuestionTitle(questionTitle);
		torf.setTrueOrFalseId(torfId);
		
		return torf;
	}

}
